﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace Labs
{
    public class SimpleDb
    {
        public static void Main()
        {
            SqlConnection con;
            con = new SqlConnection(@"Data Source=(LocalDB)\v11.0;AttachDbFilename=G:\Development\AdventureWorks2008R2_Data.mdf;Integrated Security=True;Connect Timeout=30");
            SqlDataAdapter adp = 
                new SqlDataAdapter("Select BusinessEntityID, Firstname, Lastname from Person.Person", con);
            new SqlCommandBuilder(adp);
            DataTable table = new DataTable();
            adp.Fill(table);
            QueryTable(table);
            adp.Update(table);
            
            con.Open();
            using (con)
            {
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandText = "Select Firstname, Lastname from HumanResources.vEmployee";
                SqlDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    Console.WriteLine("{0} {1}",
                        rdr.GetString(0), rdr.GetString(1));
                }

                string firstname = "'; drop database--";
                cmd.CommandText = 
          "Update Person.Person set firstname=? where businessEntityId=@id";

                cmd.Parameters.Add( firstname);
            }
 
        }

        public static void QueryTable(DataTable tbl)
        {
            
            foreach (DataRow row in tbl.Rows)
            {
                Console.WriteLine("{0} {1}",
                    row["Firstname"], row["Lastname"]);

                string name = (string)row["Firstname"];
                row["Firstname"] = name.ToUpper();

            }
            
        }
    }
}
